from flask import Flask, jsonify, abort, request
import mysql.connector

config = {
    "host": "127.0.0.1",
    "port": 3306,
    "database": "sql_store",
    "user": "root",
    "password": "Jzd@yc107"
}

app = Flask(__name__)

@app.route("/product", methods=["get"])
def getProductByUrl():
    product_id = request.args.get("id")

    conn = mysql.connector.connect(**config)
    curs = conn.cursor()

    curs.execute(f"""
    select
        product_id,
        name,
        quantity_in_stock,
        unit_price
    from
        products
    where
        product_id = {product_id}
    """)

    rst = curs.fetchall()
    jsonrst = []

    for r in rst:
        jsonrst.append({
            "product_id": int(r[0]),
            "name": r[1],
            "quantity_in_stock": int(r[2]),
            "unit_price": float(r[3])
        })
    
    curs.close()
    conn.close()

    return jsonify(jsonrst)

@app.route("/products/<int:id>", methods=["get"])
def getProductById(id):
    conn = mysql.connector.connect(**config)
    curs = conn.cursor()

    curs.execute(f"""
    select
        product_id,
        name,
        quantity_in_stock,
        unit_price
    from
        products
    where
        product_id = {id}
    """)

    rst = curs.fetchall()
    jsonrst = []

    for r in rst:
        jsonrst.append({
            "product_id": int(r[0]),
            "name": r[1],
            "quantity_in_stock": int(r[2]),
            "unit_price": float(r[3])
        })
    
    curs.close()
    conn.close()

    return jsonify(jsonrst)

@app.route("/products", methods=["get"])
def getProduct():
    conn = mysql.connector.connect(**config)
    curs = conn.cursor()

    curs.execute("""
    select
        product_id,
        name,
        quantity_in_stock,
        unit_price
    from
        products
    """)

    rst = curs.fetchall()
    jsonrst = []

    for r in rst:
        jsonrst.append({
            "product_id": int(r[0]),
            "name": r[1],
            "quantity_in_stock": int(r[2]),
            "unit_price": float(r[3])
        })
    
    curs.close()
    conn.close()

    return jsonify(jsonrst)

@app.route("/hello/<string:name>", methods=["get"])
def hello(name):
    return f"Hello, {name}!"

if __name__ == "__main__":
    app.run(port=5000, debug=True)